﻿Imports System.Windows.Forms

Public Class frmCheckTables

    Private IsCancelled As Boolean = False
    Private dgvResults As WRDB.Controls.DGVEditor

    Private Sub frmSearchTables_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        IsCancelled = True
        SaveWindowPos(REGAPP, Me)
    End Sub

    Private Sub frmSearchTables_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            GetWindowPos(REGAPP, Me)
            cboTables.SelectedIndex = 0
            lnkOpen.Visible = False
            dgResults.Visible = False
            dgvResults = New WRDB.Controls.DGVEditor(dgResults)
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub btnCheck_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCheck.Click
        Try
            btnCheck.Enabled = False
            lnkOpen.Visible = False
            Application.UseWaitCursor = True
            dgResults.Visible = False
            btnCheck.Enabled = False
            btnClose.Text = "Cancel"
            Dim dt As New DataTable
            With dt
                .Columns.Add("Data Table Name", GetType(String))
                .Columns.Add("Support Table Name", GetType(String))
                .Columns.Add("Field Name", GetType(String))
                .Columns.Add("Missing Value", GetType(String))
            End With
            With Project
                Dim tblList As New Generic.List(Of String)
                Select Case cboTables.SelectedIndex
                    Case 0 : tbllist.Add(ActiveBrowser.TableName)
                    Case 1
                        For Each frm As Form In WRDBForms
                            If TypeOf frm Is frmBrowser Then
                                With CType(frm, frmBrowser)
                                    If .TableType = clsProject.enumTableTypes.Working Or .TableType = clsProject.enumTableTypes.Master Then
                                        tbllist.Add(.TableName)
                                    End If
                                End With
                            End If
                        Next
                    Case 2 : tbllist = .DB.GetTableList
                End Select
                tblList.Sort()
                For i As Integer = 0 To tblList.Count - 1
                    Dim tblName As String = tblList(i)
                    Dim TblType As clsProject.enumTableTypes = .GetTableType(tblName)
                    If TblType = clsProject.enumTableTypes.Working Or TblType = clsProject.enumTableTypes.Master Then
                        MainForm.WriteProgressOverall(i, tblList.Count - 1, "Searching table " & tblName)
                        If Not CheckRecords(tblName, dt) Then Exit For
                    End If
                Next
                MainForm.ProgressCompleteOverall()
            End With
            With dgResults
                .DataSource = dt
                .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                .ReadOnly = True
                .RowHeadersVisible = False
                .SelectionMode = DataGridViewSelectionMode.FullRowSelect

                .AllowUserToOrderColumns = False
                .AllowUserToAddRows = False
                .AllowUserToDeleteRows = False
                For i As Integer = 0 To .Columns.Count - 1
                    With .Columns(i)
                        .SortMode = DataGridViewColumnSortMode.NotSortable
                    End With
                Next
                .ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.False
                If .RowCount < 100 Then
                    .AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
                Else
                    .AutoResizeColumns(DataGridViewAutoSizeColumnsMode.DisplayedCells)
                End If
                .Visible = True
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        Finally
            btnCheck.Enabled = True
            If cboTables.SelectedIndex = 2 Then lnkOpen.Visible = dgResults.RowCount > 0
            btnUpdate.Enabled = dgResults.RowCount > 0
            btnClose.Text = "Close"
            btnCheck.Enabled = True
            Application.UseWaitCursor = False
            MainForm.ProgressComplete()
        End Try
    End Sub

    Private Function CheckRecords(ByVal TableName As String, ByVal dt As DataTable) As Boolean
        Try
            If Not MainForm.CheckDataTableStructure(TableName, False) Then Return False
            Dim FieldName() As String = {"Station_ID", "PCode", "CCode", "RCode", "Agency"}
            Dim ValName() As String = {"Stations", "PCodes", "CCodes", "RCodes", "Agencies"}
            For i As Integer = 0 To FieldName.Length - 1

                If Not MainForm.WriteProgress(i, FieldName.Length - 1, "Field = " & FieldName(i), ProgressBarStyle.Blocks, True) Then Return False

                'get list of unique field values in data and support tables

                If Not Project.ResetPDox(TableName) Then Return False
                Dim lstSpt As Generic.List(Of String) = Project.DB.GetRecordList(TableName, FieldName(i))
                Dim lstVal As Generic.List(Of String)
                If Not Project.ResetPDox(ValName(i)) Then Return False
                lstVal = Project.DB.GetRecordList(ValName(i), FieldName(i))

                'see what fields are missing from Validation table
                Dim lstMissing As New Generic.List(Of String)
                For Each s As String In lstSpt
                    If s.Trim <> "" AndAlso Not lstVal.Contains(s) Then lstMissing.Add(s)
                Next
                For Each s As String In lstMissing
                    dt.Rows.Add(TableName, ValName(i), FieldName(i), s)
                Next
            Next
            Return True
        Catch ex As Exception
            ErrorMsg(, ex)
            Return False
        End Try
    End Function

    Private Sub btnClose_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnClose.Click
        IsCancelled = True
        If btnClose.Text = "Close" Then Me.Close()
    End Sub

    Private Sub lnkOpen_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        For i As Integer = 0 To dgResults.RowCount - 1
            Dim tblName As String = dgResults.Rows(i).Cells(0).Value
            If Not WRDBForms.Contains(tblName) Then MainForm.OpenDataTable(tblName)
        Next
    End Sub

    Private Sub btnUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Try
            Dim lstUnique As New Generic.List(Of String)
            For i As Integer = 0 To dgResults.RowCount - 1
                Dim SupportName As String = dgResults.Item(1, i).Value
                Dim FieldName As String = dgResults.Item(2, i).Value
                Dim MissingValue As String = dgResults.Item(3, i).Value

                'may have same missing values for support table from multiple data tables; only update once

                Dim Unique As String = SupportName & FieldName & MissingValue
                If Not lstUnique.Contains(Unique) Then
                    Dim sqlString As String = String.Format("INSERT INTO {0} ({1}) VALUES ('{2}')", SupportName, FieldName, MissingValue)
                    Dim NumRec As Integer = Project.DB.ExecuteNonQuery(sqlString)
                    If NumRec = -2 Then
                        If WarningMsg("Unable to add new records to table. Do you want to continue trying to update Support tables?", sqlString) = Windows.Forms.DialogResult.Cancel Then Exit Sub
                    End If
                    lstUnique.Add(Unique)
                End If
                If Not MainForm.WriteProgress(i, dgResults.RowCount - 1, "Updating Support tables...", ProgressBarStyle.Blocks, True) Then Exit For
            Next
            WarningMsg("All indicated support tables have been updated with the missing records. You now need to edit the Support tables to add additional information to the placeholder records that were just added (e.g., Name, Description, etc.).")
            btnUpdate.Enabled = False
            MainForm.FileOpenSupport()
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub
End Class